﻿Imports System.Net.Mail
Imports System.Text
Imports System.Net.NetworkCredential
Imports System.Data.SqlClient
Imports System.Data
Imports System.Collections.Generic

Partial Class driver_sick
    Inherits System.Web.UI.Page
	
    Dim connString As String = "Data Source=sql2008.campus.jcu.edu ; Initial Catalog=service; User Id=Thunder48; Password=Thundertech169;"

	Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        '        Dim parent = CType(Me.Master, driver_MasterPage)
        If Request.QueryString.Count = 0 Then ' if someone manually went to page
            Response.Redirect("portal.aspx")
        End If

        If TakenCheck() = True Then
            classInformation()
        Else
            TakenChange()
        End If



    End Sub
	
    Protected Sub classInformation()

        'Label Vars
        Dim activityname As String
        Dim begintime As Date
        Dim endtime As Date
        Dim drivetime As Integer
        Dim activityid As Integer
        Dim whichday As String

        'Sql Conn vars
        Dim conn2 As New SqlConnection
        Dim cmd2 As String
        Dim sql2 As SqlCommand = New SqlCommand
        Dim classInfo As SqlDataReader
        conn2.ConnectionString = connString
        conn2.Open()
        cmd2 = "SELECT COMSERV_ACTIVITY.[activity-name],COMSERV_ACTIVITY.[begin_time], COMSERV_ACTIVITY.[end_time], COMSERV_ACTIVITY.[travel_time], COMSERV_ACTIVITY.[activityid], Event.[Date] FROM Event INNER JOIN COMSERV_ACTIVITY ON Event.ServiceID = COMSERV_ACTIVITY.activityid where Event.ID =" & Request.QueryString("id")
        'Response.Write(cmd2)
        sql2.CommandText = cmd2
        sql2.Connection = conn2

        Try
            classInfo = sql2.ExecuteReader()
            If classInfo.Read() Then
                activityname = classInfo.GetString(0)
                begintime = classInfo.GetDateTime(1)
                endtime = classInfo.GetDateTime(2)
                drivetime = classInfo.GetInt32(3)
                activityid = classInfo.GetInt32(4)
                whichday = classInfo.GetDateTime(5).ToString("ddd, MMM d")
                'Loop
            End If
        Catch ex As Exception
            'Response.Write(ex)
        End Try

        SetLabelsUp(activityname, begintime, endtime, drivetime, activityid, whichday)


    End Sub

    Protected Sub SetLabelsUp(ByVal activityname As String, ByVal begintime As Date, ByVal endtime As Date, ByVal drivetime As Integer, ByVal activityid As Integer, ByVal whichdaystr As String)
        name.Text = "<h2>" & activityname & "<h2/>"
        begint.Text = "<br>Begin Time (Including Travel)<br/><b> " & begintime.AddMinutes(-(drivetime)).ToString("h:mm tt") & "<b/>"
        endt.Text = "<br>End Time (Including Travel)<br/><b> " & endtime.AddMinutes(drivetime).ToString("h:mm tt") & "<b/>"
        whichday.Text = "<br>On: <br/><b>" & whichdaystr & "<b/>"
        Desc.Text = "<br>Description: <a href=""" & "http://www.jcu.edu/service2/activity_detail.asp?id=" & activityid & """ target= & """ & "_blank" & """>Link to Activity</a>"
    End Sub

    Protected Function TakenCheck() As Boolean
        Dim driverid As Integer
        Dim defaultid As Integer
        'Sql Conn vars
        Dim conn3 As New SqlConnection
        Dim cmd3 As String
        Dim sql3 As SqlCommand = New SqlCommand
        Dim eventInfo As SqlDataReader
        conn3.ConnectionString = connString
        conn3.Open()
        cmd3 = "Select [DriverID],[DefaultDriverID] from [Event] where [ID] =" & Request.QueryString("id")
        sql3.CommandText = cmd3
        sql3.Connection = conn3

        Try
            eventInfo = sql3.ExecuteReader()
            If eventInfo.Read() Then
                If eventInfo.IsDBNull(0) Then

                Else
                    driverid = eventInfo.GetInt32(0)
                End If

                If eventInfo.IsDBNull(1) Then

                Else
                    defaultid = eventInfo.GetInt32(1)
                End If

                'Loop
            End If
        Catch ex As Exception
            Response.Write(ex)
        End Try

        If defaultid > 0 And driverid = 0 Then ' If there is a default driver but no driver then it needs someone
            Return True
        End If
        
        Return False
    End Function

    Protected Sub submitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submitButton.Click

        If TakenCheck() = True Then
            InsertDriver()
            EmailPeople()
            panel1.Visible = False
            panel2.Visible = True
        Else
            TakenChange()
        End If




    End Sub

    Protected Sub TakenChange()
        submitButton.Visible = False
        name.Text = "<h1>Event already taken :'( <h1/>"
    End Sub

    Protected Sub InsertDriver()
        Try
            Dim updatesql As String
            Dim updateConn As SqlConnection
            Dim updateCommand As SqlCommand

            updatesql = "UPDATE [Event] SET [DriverID] = @DriverID WHERE ID = " & Request.QueryString("id")
            updateConn = New SqlConnection(connString)
            updateCommand = New SqlCommand(updatesql, updateConn)
            updateCommand.Parameters.AddWithValue("@DriverID", Session("driver_id"))
            updateCommand.Connection = updateConn
            updateConn.Open()
            updateCommand.ExecuteNonQuery()
            updateConn.Close()
        Catch ex As Exception
            Response.Write("Something bad happened :( Unfortunately your registration probably didn't work")
        End Try
    End Sub

    Protected Sub EmailPeople()
        Dim content As New StringBuilder
        Dim emailofrequester As String

        content.Append("<tr><td>" & Session("first_name") & " " & Session("last_name") & " has taken a drive for: <br> </td></tr>")


        Dim conn As SqlConnection
        Dim command As SqlCommand
        Dim adapter As New SqlDataAdapter
        Dim ds As New DataSet
        Dim sql As String
        Dim reader As SqlDataReader



        sql = "SELECT first_name, last_name, EMail FROM Event INNER JOIN Driver ON Event.DefaultDriverID = Driver.ID where Event.ID =" & Request.QueryString("id")
        conn = New SqlConnection(connString)
        command = New SqlCommand(sql, conn)

        command.CommandText = sql
        command.Connection = conn

        Try
            conn.Open()
            reader = command.ExecuteReader()
            While reader.Read()
                content.Append("<tr><td><br>Name: " & reader(0) & " " & reader(1) & "</td></tr>")
                content.Append("<tr><td>Event: " & name.Text & "</td></tr>")
                content.Append("<tr><td>" & whichday.Text & "</td></tr>")
                content.Append("</table>")
                emailofrequester = reader(2)
            End While
            conn.Close()
        Catch ex As Exception
            'Response.Write(ex)
        End Try

        'This is where it actually sends the message

        Dim mailServerName As String = "smtp.gmail.com"

        Dim mailAccount As New System.Net.NetworkCredential("cssaliaisons@jcu.edu", "driver2012")

        Dim message As MailMessage = New MailMessage("cssaliaisons@jcu.edu", "cssaliaisons@jcu.edu", "Notification: Student Driver Substitute Filled", content.ToString)

        message.CC.Add(emailofrequester)
        message.CC.Add(Session("email"))

        message.IsBodyHtml = True

        Dim mailClient As SmtpClient = New SmtpClient

        Try
            mailClient.Host = mailServerName
            mailClient.Port = 587
            mailClient.EnableSsl = True
            mailClient.UseDefaultCredentials = False
            mailClient.Credentials = mailAccount
            mailClient.Send(message)
        Catch ex As Exception
            'Response.Write(ex)
            Response.Write("Failure sending substitute notice.")
        End Try

        message.Dispose()

    End Sub
End Class
